﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Reflection;
using Microsoft.Office.Interop.Excel;

namespace ExcelAutomator
{
	public static class ExcelHelper
	{
		public static Workbook OpenWorkbook(this Application application, string fileNamePath)
		{
			return application.Workbooks.Open(fileNamePath, 0, false, 5, Missing.Value, Missing.Value, false, Missing.Value, Missing.Value, true, false, Missing.Value, false, Missing.Value, Missing.Value);
		}

		#region Range.Value<T>

		public static T Value<T>(this Range range)
		{
			if (range.Value2 != null)
			{
				return (T)range.Value2;
			}
			return default(T);
		}

		#endregion

		#region Range.Values<T>

		public static IEnumerable<T> Values<T>(this Range range)
		{
			if(range.Value2 != null)
			{
				var arr = range.Value2 as Array;
				if(arr != null)
				{
					foreach (var value in arr)
					{
						if(value == null)
						{
							yield return default(T);
						}
						else if (value.GetType() == typeof(T))
						{
							yield return (T) value;
						}
						else
						{
							continue;
						}
					}
				}
				else
				{
					yield return range.Value<T>();
				}
			}
			else
			{
				yield return default(T);
			}
		}

		#endregion

		#region GetLastRow

		public static int GetLastRow(this Worksheet sh, int columnIndex)
		{
			return ((Range) sh.Cells[sh.Rows.Count, columnIndex]).get_End(XlDirection.xlUp).Row;
		}

		#endregion

		#region GetLastRowWithData

		public static long GetLastRowWithData(this Worksheet sh)
		{
			return sh.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing).Row;
		}

		#endregion

		#region TryGetWorksheet

		public static bool TryGetWorksheet(this Workbook workbook, string name, out Worksheet worksheet)
		{
			if (name.Contains('!'))
			{
				name = name.Split('!')[0];
			}

			foreach (Worksheet sheet in workbook.Sheets)
			{
				if (sheet.Name.Equals(name, StringComparison.OrdinalIgnoreCase))
				{
					worksheet = sheet;
					return true;
				}
			}
			worksheet = null;
			return false;
		}

		#endregion

		public static void Save(this Worksheet worksheet, string filename, XlFileFormat fileFormat)
		{
			worksheet.SaveAs(filename, fileFormat, Type.Missing, Type.Missing, false, false, Type.Missing, Type.Missing, Type.Missing, Missing.Value);
		}

		public static void SaveAs(this Workbook workbook, string filename, XlFileFormat fileFormat)
		{
			workbook.SaveAs(filename, fileFormat, Type.Missing, Type.Missing, false, false, XlSaveAsAccessMode.xlShared, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Missing.Value);
		}

		public static bool TryGetWorkbook(this Worksheet worksheet, out Workbook workbook)
		{
			foreach (Workbook book in worksheet.Application.Workbooks)
			{
				foreach (Worksheet sheet in book.Worksheets)
				{
					if (sheet.GetHashCode() == worksheet.GetHashCode())
					{
						workbook = book;
						return true;
					}
				}
			}
			workbook = null;
			return false;

		}

		public static void RunMacro(this Application application, string workbookName, string macroName)
		{
			var m = Type.Missing;
			application.Run(string.Format("{0}!{1}", workbookName, macroName), 
				m, m, m, m, m, m, m, m, m, m,
				m, m, m, m, m, m, m, m, m, m,
				m, m, m, m, m, m, m, m, m, m);
		}
	}
}
